package userDao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import bookDao.BookDbconnection;
import table.User;

public class UserImpl implements UserDao{
	String sql = null;
	String sql1 = null;
	int result = 0;
	int result1 = 0;
	private User user;
	@Override 
	public List<User> findAll() {
		Connection con = null; 
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<User> UserList = new ArrayList<User>();
		try {
			con = BookDbconnection.getConnection();
			sql = "SELECT user_id,user_name,user_phoneNumber,login_password,user_sex,login_name,user_identity FROM user";
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				int id = rs.getInt(1);
				String userName = rs.getString(2);
				String userPhoneNumber = rs.getString(3);
				String loginPassword = rs.getString(4);
				String userSex = rs.getString(5);
				String loginName = rs.getString(6);
				String identity = rs.getString(7);
				User user = new User(id,userName,userPhoneNumber,loginPassword,userSex,loginName,identity);
				UserList.add(user);			
			}
		}
		catch(SQLException e) {
			e.printStackTrace();
		}finally {
			BookDbconnection.close(rs,ps);
		}
		return UserList;
	}

	@Override
	public void save(User x) {
		Connection con = null;
		PreparedStatement ps = null;
		PreparedStatement ps1 = null;
		ResultSet rs = null;   
		ResultSet rs1 = null; 
		try {
			con = BookDbconnection.getConnection();
			sql = "INSERT INTO user VALUE(null,?,?,?,?,?,?)";
			sql1 = "INSERT INTO login VALUE(null,?,?)";
			ps = con.prepareStatement(sql);
			ps1 = con.prepareStatement(sql1);
			ps.setString(1,x.getUserName());  
			ps.setString(2, x.getUserPhoneNumber());
			ps.setString(3, x.getLoginPassword());
			ps.setString(4, x.getUserSex());
			ps.setString(5,x.getLoginName());
			ps.setString(6,x.getIdentity());
			ps1.setString(1,x.getLoginName());
			ps1.setString(2,x.getLoginPassword());
			result = ps.executeUpdate();
			result1 = ps1.executeUpdate();
			System.out.println("操作结果是"+result);
			System.out.println("操作结果是"+result1);
		}
		catch(SQLException e) {
			e.printStackTrace();
		}finally {
			BookDbconnection.close(rs,ps);
			BookDbconnection.close(rs1,ps1);
		}		
	}

	/**
	 *x为用户名（学号或教工号）注意系统管理员不能删除
	 */
	@Override
	public void delete(int x) { 
		Connection con = null;
		PreparedStatement ps = null; 
		ResultSet rs = null;
		try {
			con = BookDbconnection.getConnection();
			sql = "DELETE FROM user WHERE user_id=?";
			ps = con.prepareStatement(sql);
			ps.setInt(1, x);
			result = ps.executeUpdate();
			System.out.println("操作结果是" + result);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			BookDbconnection.close(rs, ps);
		}
	}

	/**
	 *系统管理员 修改用户的个人信息 
	 */
	@Override
	public void update(User x) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			con = BookDbconnection.getConnection();
			sql = "UPDATE user SET user_name=?,user_phoneNumber=?,login_password=?,user_sex=?,user.login_name=?,user_identity=? WHERE user_id=?";
			ps = con.prepareStatement(sql);
			ps.setString(1, x.getUserName());			 
			ps.setString(2,x.getUserPhoneNumber()); 
			ps.setString(3,x.getLoginPassword()); 
			ps.setString(4,x.getUserSex());
			ps.setString(5, x.getLoginName());
			ps.setString(6, x.getIdentity());
			ps.setInt(7, x.getUserId()); 
			result = ps.executeUpdate();  
			System.out.println("操作结果是"+result);
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			BookDbconnection.close(rs, ps);
		}	
		
	} 
	//通过三个字段来查询
	/**
	 *通过用户的 用户名 姓名 身份查询
	 */
	@Override
	public List<User> get(String x,String y,String z) {
		Connection con = null; 
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<User> UserList = new ArrayList<User>();
		try {
			con = BookDbconnection.getConnection();
			sql = "SELECT user_id,user_name,user_phoneNumber,login_password,user_sex,login_name,user_identity FROM user WHERE login_name LIKE ? and user_name LIKE ? and user_identity=?";
			ps = con.prepareStatement(sql);
			ps.setString(1, '%'+x+'%');
			ps.setString(2, '%'+y+'%');
			ps.setString(3, z);
			rs = ps.executeQuery();
			while(rs.next()) {
				int id = rs.getInt(1);
				String userName = rs.getString(2);
				String userPhoneNumber = rs.getString(3);
				String loginPassword = rs.getString(4);
				String userSex = rs.getString(5);
				String loginName = rs.getString(6);
				String identity = rs.getString(7); 
				User user = new User(id,userName,userPhoneNumber,loginPassword,userSex,loginName,identity);
				UserList.add(user);			
			}
		}
		catch(SQLException e) {
			e.printStackTrace();
		}finally {
			BookDbconnection.close(rs,ps);
		}
		return UserList;
	}
	//通过身份和用户名 查询 
	public List<User> get1(String x,String z) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<User> UserList = new ArrayList<User>();
		try {
			con = BookDbconnection.getConnection();
			sql = "SELECT user_id,user_name,user_phoneNumber,login_password,user_sex,login_name,user_identity FROM user WHERE login_name LIKE ? and user_identity=?";
			ps = con.prepareStatement(sql);
			ps.setString(1, '%'+x+'%');
			ps.setString(2, z);
			rs = ps.executeQuery();
			while(rs.next()) {
				int id = rs.getInt(1);
				String userName = rs.getString(2);
				String userPhoneNumber = rs.getString(3);
				String loginPassword = rs.getString(4);
				String userSex = rs.getString(5);
				String loginName = rs.getString(6);
				String identity = rs.getString(7);
				User user = new User(id,userName,userPhoneNumber,loginPassword,userSex,loginName,identity);
				UserList.add(user);			
			}
		}
		catch(SQLException e) {
			e.printStackTrace();
		}finally {
			BookDbconnection.close(rs,ps);
		}
		return UserList;
	}
	//通过姓名和身份 查询
	public List<User> get2(String y,String z) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<User> UserList = new ArrayList<User>();
		try {
			con = BookDbconnection.getConnection();
			sql = "SELECT user_id,user_name,user_phoneNumber,login_password,user_sex,login_name,user_identity FROM user WHERE user_name LIKE ? and user_identity=?";
			ps = con.prepareStatement(sql);
			ps.setString(1, '%'+y+'%');
			ps.setString(2, z);
			rs = ps.executeQuery();
			while(rs.next()) {
				int id = rs.getInt(1);
				String userName = rs.getString(2);
				String userPhoneNumber = rs.getString(3);
				String loginPassword = rs.getString(4);
				String userSex = rs.getString(5);
				String loginName = rs.getString(6);
				String identity = rs.getString(7);
				User user = new User(id,userName,userPhoneNumber,loginPassword,userSex,loginName,identity);
				UserList.add(user);			
			}
		}
		catch(SQLException e) {
			e.printStackTrace();
		}finally {
			BookDbconnection.close(rs,ps);
		}
		return UserList;
	}

	/**
	 *通过用户的身份查询
	 */
	@Override
	public List<User> get3(String z) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<User> UserList = new ArrayList<User>();
		try {
			con = BookDbconnection.getConnection();
			sql = "SELECT user_id,user_name,user_phoneNumber,login_password,user_sex,login_name,user_identity FROM user WHERE user_identity=?";
			ps = con.prepareStatement(sql);
			ps.setString(1, z);
			rs = ps.executeQuery();
			while(rs.next()) {
				int id = rs.getInt(1);
				String userName = rs.getString(2);
				String userPhoneNumber = rs.getString(3);
				String loginPassword = rs.getString(4);
				String userSex = rs.getString(5);
				String loginName = rs.getString(6);
				String identity = rs.getString(7);
				User user = new User(id,userName,userPhoneNumber,loginPassword,userSex,loginName,identity);
				UserList.add(user);			
			}
		}
		catch(SQLException e) {
			e.printStackTrace();
		}finally {
			BookDbconnection.close(rs,ps);
		}
		return UserList;
	}
	/**
	 * @param x
	 * 通过userId 查询用户的姓名 性别 用户图书管理员的 图书借阅功能
	 */
	@Override
	public List<User> get4(int x) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<User> UserList = new ArrayList<User>();
		try {
			con = BookDbconnection.getConnection();
			sql = "SELECT user_name,user_phoneNumber,login_password,user_sex,login_name,user_identity FROM user WHERE user_id=?";
			ps = con.prepareStatement(sql);
			ps.setInt(1, x);
			rs = ps.executeQuery();
			while(rs.next()) {
				String userName = rs.getString(1);
				String userPhoneNumber = rs.getString(2);
				String loginPassword = rs.getString(3);
				String userSex = rs.getString(4); 
				String loginName = rs.getString(5);
				String identity = rs.getString(6);
				User user = new User(x,userName,userPhoneNumber,loginPassword,userSex,loginName,identity);
				UserList.add(user);			
			}
		}
		catch(SQLException e) {
			e.printStackTrace();
		}finally {
			BookDbconnection.close(rs,ps);
		}
		return UserList;
	}
	//通过用户名找用户信息
			public List<User> get5(String x) {
				Connection con = null;
				PreparedStatement ps = null;
				ResultSet rs = null;
				List<User> UserList = new ArrayList<User>();
				try {
					con = BookDbconnection.getConnection();
					sql = "SELECT user_id,user_name,user_phoneNumber,login_password,user_sex,login_name,user_identity FROM user WHERE login_name LIKE ?";
					ps = con.prepareStatement(sql);
					ps.setString(1, '%'+x+'%');
					rs = ps.executeQuery();
					while(rs.next()) {
						int id = rs.getInt(1);
						String userName = rs.getString(2);
						String userPhoneNumber = rs.getString(3);
						String loginPassword = rs.getString(4);
						String userSex = rs.getString(5);
						String loginName = rs.getString(6);
						String identity = rs.getString(7);
						User user = new User(id,userName,userPhoneNumber,loginPassword,userSex,loginName,identity);
						UserList.add(user);			
					}
				}
				catch(SQLException e) {
					e.printStackTrace();
				}finally {
					BookDbconnection.close(rs,ps);
				}
				return UserList;
			}
	/**
	 *
	 *用于修改操作时 拿到有数据表选中的行的 用户的userId
	 */
	@Override
	public int getUserId(String x) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int userId = 0; 
		try {
			con = BookDbconnection.getConnection();
			sql = "SELECT user_id FROM user WHERE login_Name=?";
			ps = con.prepareStatement(sql);
			ps.setString(1, x); 
			rs = ps.executeQuery();
			while(rs.next()) {
			userId = rs.getInt(1);
			}
		}
		catch(SQLException e) {
			e.printStackTrace();
		}finally {
			BookDbconnection.close(rs,ps);
		}
		return userId;
	}
	public User getUser() {
		return user;
	}

	public void setUser(User user) {
		this.user = user;
	}

	@Override
	public void updateByOneself(User x) {
		Connection con = null;
		PreparedStatement ps = null;
		PreparedStatement ps1 = null;
		ResultSet rs = null;	
		ResultSet rs1 = null;
		String sql1 ;
		int result1 = 0;
		try {
			con = BookDbconnection.getConnection();
			sql = "UPDATE user SET user_name=?,user_phoneNumber=?,login_password=?,user_sex=? WHERE login_name=?";
			sql1 = "UPDATE login SET login_password=? WHERE login_name=?";
			ps = con.prepareStatement(sql);
			ps1 = con.prepareStatement(sql1);
			ps.setString(1, x.getUserName());			
			ps.setString(2,x.getUserPhoneNumber()); 
			ps.setString(3, x.getLoginPassword());
			ps.setString(4,x.getUserSex()); 
			ps.setString(5, x.getLoginName());
			ps1.setString(1, x.getLoginPassword());
			ps1.setString(2, x.getLoginName());
			result = ps.executeUpdate(); 
			result1 = ps1.executeUpdate();
			System.out.println("操作结果是"+result);
			System.out.println("操作结果是"+result1);
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			BookDbconnection.close(rs, ps);
			BookDbconnection.close(rs1, ps1);
		}	
		
	}
	 
}
